package com.guit.hrms.utils;

import com.guit.hrms.po.resume.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.lang.reflect.Field;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelUtil {

    private static final Map<String, FieldPosition> fieldPositionMap = new HashMap<>();

//    public static void main (String[] args) throws IOException {
//        EmployeeResume employeeResume = new EmployeeResume();
////        employeeResume.setBasicInfo(new BasicInfo("张三", "男", "1991-09-01", "021-80187116", "shekuwang@123.com"));
////        employeeResume.setEducations(List.of(
////                new Education("摄影大学", "世界经济系", "本科", "2013年7月"),
////                new Education("摄影大学", "世界经济系", "本科", "2013年7月")
////        ) /*null*/);
//        employeeResume.setWorkExperiences(List.of(
//                new WorkExperience("青德基餐饮有限公司", "经理助理", "2011-2012", "负责日常管理和协调工作"),
//                new WorkExperience("青德基**公司", "经理助理", "2011-2012", "负责日常管理和协调工作")
//        ) /*null*/);
////        employeeResume.setResearchAchievements(List.of(
////                new ResearchAchievement("某某科研项目", "2012-2013", "研究某某领域的前沿技术"),
////                new ResearchAchievement("某某科研项目", "2012-2013", "研究某某领域的前沿技术")
////        ) /*null*/);
////        employeeResume.setTeachingExperiences(List.of(
////                new TeachingExperience("经济学导论", "2013-2014", "为本科生讲授经济学基础知识"),
////                new TeachingExperience("经济学导论", "2013-2014", "为本科生讲授经济学基础知识")
////        ) /*null*/);
////        employeeResume.setAwards(List.of(
////                new Award("优秀员工奖", "2014年", "青德基餐饮有限公司"),
////                new Award("优秀员工奖", "2015年", "青德基餐饮有限公司")
////        ) /*null*/);
////        employeeResume.setSkills(List.of(
////                new Skill("摄影", "精通"),
////                new Skill("绘画", "熟练")
////        ) /*null*/);
////        employeeResume.setSocialServices(List.of(
////                new SocialService("社区志愿者", "2015年", "参与社区公益活动"),
////                new SocialService("社区志愿者", "2015年", "参与社区公益活动")
////        ) /*null*/);
////        employeeResume.setSelfEvaluation("本人思想锐意向上，性格开朗，积极进取，有较强的团队精神。在校期间所学知识较为局限，我不断学习以提高自己的知识水平。我有丰富的实践经验，能够在工作中取得优异成绩。" /*null*/);
//
//        EmployeeResumeFile excel = createResumeExcel(employeeResume);
////        try (FileOutputStream fileOut = new FileOutputStream("EmployeeResumeTemplate.xlsx")) {
////            fileOut.write(excel.getFileContent());
////            System.out.println("Excel模板已生成: EmployeeResumeTemplate.xlsx");
////        }
//
////        employeeResume.setBasicInfo(/*new BasicInfo("张三", "男", "1991-09-01", "021-80187116", "shekuwang@123.com")*/ null);
////        employeeResume.setEducations(List.of(
////                new Education("摄影大学", "世界经济系", "本科", "2013年7月"),
////                new Education("摄影大学", "世界经济系", "本科", "2013年7月")
////        ) /*null*/);
//
//        employeeResume.setWorkExperiences(List.of(
//                new WorkExperience("青德基餐饮有限公司", "经理助理", "2011-2012", "负责日常管理和协调工作"),
//                new WorkExperience("青德基餐饮有限公司", "经理助理", "2011-2012", "负责日常管理和协调工作")
//        ) /*null*/);
//
////        employeeResume.setResearchAchievements(List.of(
////                new ResearchAchievement("某某科研项目", "2012-2013", "研究某某领域的前沿技术"),
////                new ResearchAchievement("某某科研项目", "2012-2013", "研究某某领域的前沿技术")
////        ) /*null*/);
////        employeeResume.setTeachingExperiences(List.of(
////                new TeachingExperience("经济学导论", "2013-2014", "为本科生讲授经济学基础知识"),
////                new TeachingExperience("经济学导论", "2013-2014", "为本科生讲授经济学基础知识")
////        ) /*null*/);
////        employeeResume.setAwards(List.of(
////                new Award("优秀员工奖", "2014年", "青德基餐饮有限公司"),
////                new Award("优秀员工奖", "2015年", "青德基餐饮有限公司")
////        ) /*null*/);
////        employeeResume.setSkills(List.of(
////                new Skill("摄影", "精通"),
////                new Skill("绘画", "熟练")
////        ) /*null*/);
////        employeeResume.setSocialServices(List.of(
////                new SocialService("社区志愿者", "2015年", "参与社区公益活动"),
////                new SocialService("社区志愿者", "2015年", "参与社区公益活动")
////        ) /*null*/);
////        employeeResume.setSelfEvaluation(/*"本人思想锐意向上，性格开朗，积极进取，有较强的团队精神。在校期间所学知识较为局限，我不断学习以提高自己的知识水平。我有丰富的实践经验，能够在工作中取得优异成绩。"*/ null);
//
//        String[] content = convertEmployeeResumeToFieldContents(employeeResume);
//
//        // 解析 Excel 文件
//        Workbook workbook = new XSSFWorkbook(new ByteArrayInputStream(excel.getFileContent()));
//
////        byte[] excelBytes1 = null;
//
////        for (int i = 1; i < 8; i++) {
//        int i = 3;
//        int insertRow = 0;
//        if (content.length != 0 && fieldPositionMap.get("fieldContents[" + i + "]") != null) {
//            System.out.println(Arrays.deepToString(content));
//            insertRow = fieldPositionMap.get("fieldContents[" + i + "]").getRow();
//            System.out.println(insertRow);
//        } else {
//            insertRow = workbook.getSheetAt(0).getLastRowNum()+1;
//            System.out.println(insertRow);
//        }
//
//        excel.setFileContent(insertRowIntoResumeExcel(workbook, fieldPositionMap, i, insertRow, content).getFileContent());
////        }
//        System.out.println(fieldPositionMap);
//
//        try (FileOutputStream fileOut = new FileOutputStream("EmployeeResumeTemplate.xlsx")) {
//            fileOut.write(excel.getFileContent());
//            System.out.println("Excel模板已生成: EmployeeResumeTemplate.xlsx");
//        } finally {
//            workbook.close();
//        }
//    }

    public static EmployeeResumeFile createResumeExcel (EmployeeResume employeeResume) throws IOException {
        // 创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("员工个人履历");

        // 创建样式
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        CellStyle subHeaderStyle = workbook.createCellStyle();
        Font subHeaderFont = workbook.createFont();
        subHeaderFont.setBold(true);
        subHeaderStyle.setFont(subHeaderFont);
        subHeaderStyle.setAlignment(HorizontalAlignment.CENTER);
        subHeaderStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        CellStyle dataStyle = createDataStyle(workbook);

        // 定义字段和子字段
        String[] fields =
//                {"基本信息", "姓名", "性别", "出生年月", "联系电话", "电子邮箱"},
//                {"教育背景", "学校", "专业", "学历", "毕业时间"},
                {"工作经历", "学校部门", "职位", "工作时间", "工作描述"};
//                {"科研成果", "项目名称", "项目时间", "项目描述"},
//                {"教学经历", "课程名称", "教学时间", "教学描述"},
//                {"获奖情况", "奖项名称", "获奖时间", "颁奖单位"},
//                {"专业技能", "技能名称", "熟练程度"},
//                {"社会服务", "服务名称", "服务时间", "服务描述"},
//                {"自我评价"}

        System.out.println("employeeResume:\n"+employeeResume);

        String[] fieldContents = convertEmployeeResumeToFieldContents(employeeResume);

        // 写入字段和内容
        int rowNum = 0;
        Row topRow = sheet.createRow(rowNum++);
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,6));
        Cell topCell = topRow.createCell(0);
        topCell.setCellValue("员工个人履历 ");
        topCell.setCellStyle(subHeaderStyle);

        // 写入字段名称
        Row fieldRow = sheet.createRow(rowNum++);
        sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,0,6));
        Cell fieldCell = fieldRow.createCell(0);
        fieldCell.setCellValue(fields[0]);
        fieldCell.setCellStyle(headerStyle);
//        for (int i = 1; i < fields.length; i++){
            // 写入子字段名称
        Row subFieldRow = null;
//            if (i == 0){
//
//                subFieldRow = sheet.createRow(rowNum++);
//                //姓名
//                Cell subFieldCell1 = subFieldRow.createCell(0);
//                subFieldCell1.setCellValue(fields[i][1]);
//                subFieldCell1.setCellStyle(subHeaderStyle);
//
//                Cell contentCeil1 = subFieldRow.createCell(1);
//                contentCeil1.setCellStyle(dataStyle);
//                fieldPositionMap.put("name", new FieldPosition(rowNum - 1, 1));
//
//                //性别
//                Cell subFieldCell2 = subFieldRow.createCell(2);
//                subFieldCell2.setCellValue(fields[i][2]);
//                subFieldCell2.setCellStyle(subHeaderStyle);
//
//                Cell contentCeil2 = subFieldRow.createCell(3);
//                contentCeil2.setCellStyle(dataStyle);
//                fieldPositionMap.put("gender", new FieldPosition(rowNum - 1, 3));
//
//                //出生年月
//                Cell subFieldCell3 = subFieldRow.createCell(4);
//                subFieldCell3.setCellValue(fields[i][3]);
//                subFieldCell3.setCellStyle(subHeaderStyle);
//                sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,5,6));
//
//                Cell contentCeil3 = subFieldRow.createCell(5);
//                contentCeil3.setCellStyle(dataStyle);
//                fieldPositionMap.put("birthDate", new FieldPosition(rowNum - 1, 5));
//
//                //联系电话
//                subFieldRow = sheet.createRow(rowNum++);
//                Cell subFieldCell4 = subFieldRow.createCell(0);
//                subFieldCell4.setCellValue(fields[i][4]);
//                subFieldCell4.setCellStyle(subHeaderStyle);
//                sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,1,3));
//
//                Cell contentCeil4 = subFieldRow.createCell(1);
//                contentCeil4.setCellStyle(dataStyle);
//                fieldPositionMap.put("phone", new FieldPosition(rowNum - 1, 1));
//
//                //电子邮箱
//                Cell subFieldCell5 = subFieldRow.createCell(4);
//                subFieldCell5.setCellValue(fields[i][5]);
//                subFieldCell5.setCellStyle(subHeaderStyle);
//                sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,5,6));
//
//                Cell contentCeil5 = subFieldRow.createCell(5);
//                contentCeil5.setCellStyle(dataStyle);
//                fieldPositionMap.put("email", new FieldPosition(rowNum - 1, 5));
//
//                setMsg(sheet, fieldContents[i] == null ? null : fieldContents[i][0]);
//
//            }
//            if (fields[i].length > 1){
        subFieldRow = sheet.createRow(rowNum++);
//            }
//            if(i == 1 || i == 2){
        mergedRegin1(sheet, subHeaderStyle, fields,/* i,*/ rowNum, subFieldRow, 1);
//            }
//            if(i == 3 || i == 4 || i == 5 || i == 7){
//                mergedRegin2(sheet, subHeaderStyle, fields, i, rowNum, subFieldRow, 1);
//            }
//            if (i == 6){
//                mergedRegin3(sheet, subHeaderStyle, fields, i, rowNum, subFieldRow, 1);
//            }

//        System.out.println("fieldContents[" + i + "] is " + fieldContents[i]);
        int i1 = 0;
        // 写入内容
        if (Arrays.equals(/*Arrays.deepToString(*/fieldContents/*)*/, /*Arrays.deepToString(*/new String[]{}/*)*/)) {
            System.out.println("fieldContents is null");
            Row contentRow = sheet.createRow(rowNum++);
//                if(i == 1 || i == 2){
            mergedRegin1(sheet, dataStyle, new String[]{}, /*i1,*/ rowNum, contentRow, 0);
//                }
//                if(i == 3 || i == 4 || i == 5 || i == 7){
//                    mergedRegin2(sheet, dataStyle, new String[][]{}, i1, rowNum, contentRow, 0);
//                }
//                if (i == 6){
//                    mergedRegin3(sheet, dataStyle, new String[][]{}, i1, rowNum, contentRow, 0);
//                }
            fieldPositionMap.put("fieldContents[" + i1 + "]", new FieldPosition(rowNum - 1, 0));
        } else {
            System.out.println("fieldContents is "+ Arrays.toString(fieldContents));
            for (int i = 0;i < fieldContents.length; i++) {
                Row contentRow = sheet.createRow(rowNum++);
//                if(i == 1 || i == 2){
                mergedRegin1(sheet, dataStyle, new String[]{fieldContents[i]}, /*i1++,*/ rowNum, contentRow, 0);
                i1++;
//                }
//                if(i == 3 || i == 4 || i == 5 || i == 7){
//                    mergedRegin2(sheet, dataStyle, fieldContents[i], i1++, rowNum, contentRow, 0);
//                }
//                if (i == 6){
//                    mergedRegin3(sheet, dataStyle, fieldContents[i], i1++, rowNum, contentRow, 0);
//                }
//                if (i == fields.length - 1) {
//                    mergedRegin4(sheet, dataStyle, content, rowNum, contentRow);
//                }
                fieldPositionMap.put("fieldContents[" + i1 + "]", new FieldPosition(rowNum - 1, 1));
            }
        }

//        }

        // 调整列宽
        sheet.setColumnWidth(4, 14*256);

        // 将工作簿写入字节数组
        System.out.println("fieldPositionMap: " + fieldPositionMap);
        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            workbook.write(outputStream);
            EmployeeResumeFile employeeResumeFile = new EmployeeResumeFile();
            employeeResumeFile.setFileContent(outputStream.toByteArray());
            employeeResumeFile.setFieldPositions(JsonUtil.toJson(fieldPositionMap));
            return employeeResumeFile;
        } finally {
            // 关闭工作簿
            workbook.close();
        }
    }

    public static EmployeeResumeFile insertRowIntoResumeExcel(Workbook workbook,Map<String, FieldPosition> fieldPositionMap, int i, int insertRow, String[] newData) throws IOException {
        Sheet sheet = workbook.getSheetAt(0);
//        System.out.println("insertRow is " + insertRow);
//        if (insertRow < sheet.getLastRowNum()){
//            sheet.shiftRows(insertRow, sheet.getLastRowNum(), newData.length);
//        }
//
//        System.out.println("fieldPositionMap.size() "+fieldPositionMap.size());
//        for (int num = i;num <= fieldPositionMap.size(); num++){
//            fieldPositionMap.get("fieldContents[" + num + "]").setRow(fieldPositionMap.get("fieldContents[" + num + "]").getRow() + newData.length);
//        }
//        System.out.println("fieldPositionMap "+fieldPositionMap);
        CellStyle dataStyle = createDataStyle(workbook);

        int i1 = fieldPositionMap.size() + 1;
        for (String content : newData) {
            Row contentRow = sheet.createRow(insertRow++);
//            if(i == 1 || i == 2){
            mergedRegin1(sheet, dataStyle, newData,/* i1++,*/ insertRow, contentRow, 0);
            fieldPositionMap.put("fieldContents[" + i1++ + "]", new FieldPosition(insertRow - 1, 0));
//            }
//            if(i == 3 || i == 4 || i == 5 || i == 7){
//                mergedRegin2(sheet, dataStyle, newData, i1++, insertRow, contentRow, 0);
//            }
//            if (i == 6){
//                mergedRegin3(sheet, dataStyle, newData, i1++, insertRow, contentRow, 0);
//            }
        }

        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            workbook.write(outputStream);
            EmployeeResumeFile employeeResumeFile = new EmployeeResumeFile();
            employeeResumeFile.setFileContent(outputStream.toByteArray());
            employeeResumeFile.setFieldPositions(JsonUtil.toJson(fieldPositionMap));
            System.out.println("fieldPositionMap: "+fieldPositionMap);
            return employeeResumeFile;
        } /*finally {
            // 关闭工作簿
            workbook.close();
        }*/
    }

//    public static void importResumeExcel(EmployeeResume employeeResume) throws IOException {
//
//    }
//
//    public static void exportResumeToExcel(EmployeeResume employeeResume) throws IOException {
//
//    }

    public static CellStyle createDataStyle(Workbook workbook){
        CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setWrapText(true);
        dataStyle.setAlignment(HorizontalAlignment.CENTER);
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return dataStyle;
    }

    /**
     * 将员工简历对象转换为Excel字段内容
     * @param employeeResume 员工简历对象
     * @return Excel字段内容
     */
    public static String[] convertEmployeeResumeToFieldContents(EmployeeResume employeeResume){
        String[] fieldContents;

//        // 基本信息
//        fieldContents[0] = employeeResume.getBasicInfo() == null ? new String[][]{} : new String[][]{{
//                employeeResume.getBasicInfo().getName(),
//                employeeResume.getBasicInfo().getGender(),
//                employeeResume.getBasicInfo().getBirthDate(),
//                employeeResume.getBasicInfo().getPhone(),
//                employeeResume.getBasicInfo().getEmail()
//        }};

//        // 教育背景
//        fieldContents[1] = employeeResume.getEducations() == null ? new String[][]{} : employeeResume.getEducations().stream()
//                                                                        .map(education -> new String[]{
//                                                                                education.getSchool(),
//                                                                                education.getMajor(),
//                                                                                education.getDegree(),
//                                                                                education.getGraduationDate()
//                                                                        })
//                                                                        .toArray(String[][]::new);

        // 工作经历
        fieldContents = employeeResume.getWorkExperiences() == null ? new String[]{} : employeeResume.getWorkExperiences().stream()
                                                                            .map(WorkExperience::toString)
                                                                            .toArray(String[]::new);

//        // 科研成果
//        fieldContents[3] = employeeResume.getResearchAchievements() == null ? new String[][]{} : employeeResume.getResearchAchievements().stream()
//                                                                                    .map(research -> new String[]{
//                                                                                            research.getProjectName(),
//                                                                                            research.getPeriod(),
//                                                                                            research.getDescription()
//                                                                                    })
//                                                                                    .toArray(String[][]::new);
//
//        // 教学经历
//        fieldContents[4] = employeeResume.getTeachingExperiences() == null ? new String[][]{} : employeeResume.getTeachingExperiences().stream()
//                                                                                    .map(teaching -> new String[]{
//                                                                                            teaching.getCourseName(),
//                                                                                            teaching.getPeriod(),
//                                                                                            teaching.getDescription()
//                                                                                    })
//                                                                                    .toArray(String[][]::new);
//
//        // 获奖情况
//        fieldContents[5] = employeeResume.getAwards() == null ? new String[][]{} : employeeResume.getAwards().stream()
//                                                                                        .map(award -> new String[]{
//                                                                                                award.getAwardName(),
//                                                                                                award.getAwardDate(),
//                                                                                                award.getAwardingInstitution()
//                                                                                        })
//                                                                                        .toArray(String[][]::new);
//
//        // 专业技能
//        fieldContents[6] = employeeResume.getSkills() == null ? new String[][]{} : employeeResume.getSkills().stream()
//                                                                                        .map(skill -> new String[]{
//                                                                                                skill.getSkillName(),
//                                                                                                skill.getProficiency()
//                                                                                        })
//                                                                                        .toArray(String[][]::new);
//
//        // 社会服务
//        fieldContents[7] = employeeResume.getSocialServices() == null ? new String[][]{} : employeeResume.getSocialServices().stream()
//                                                                                                .map(service -> new String[]{
//                                                                                                        service.getServiceName(),
//                                                                                                        service.getPeriod(),
//                                                                                                        service.getDescription()
//                                                                                                })
//                                                                                                .toArray(String[][]::new);
//
//        // 自我评价
//        fieldContents[8] = new String[][]{{
//                employeeResume.getSelfEvaluation()
//        }};
        return fieldContents;
    }

//    private static void setMsg(Sheet sheet, String[] content) {
//        sheet.getRow(2).getCell(1).setCellValue(content[0]);
//        sheet.getRow(2).getCell(3).setCellValue(content[1]);
//        sheet.getRow(2).getCell(5).setCellValue(content[2]);
//        sheet.getRow(3).getCell(1).setCellValue(content[3]);
//        sheet.getRow(3).getCell(5).setCellValue(content[4]);
//    }

    private static void mergedRegin1(Sheet sheet, CellStyle dataStyle, String[] content, /*int i,*/ int rowNum, Row contentRow, int type) {
        sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,0,1));
        sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,2,3));
        sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,5,6));
        System.out.println(Arrays.toString(content.length == 0 ? null : content));

        for (String contentItem : content) {
            if (contentItem.contains("WorkExperience")) {
                Cell contentCell = null;
                // 使用正则表达式匹配并提取字段值
                Pattern pattern = Pattern.compile("WorkExperience\\(company=(.*?), position=(.*?), period=(.*?), description=(.*?)\\)");
                Matcher matcher = pattern.matcher(contentItem);
                if (matcher.find()) {
                    String company = matcher.group(1);
                    String position = matcher.group(2);
                    String period = matcher.group(3);
                    String description = matcher.group(4);

                    contentCell = contentRow.createCell(0);
                    contentCell.setCellValue(company);
                    contentCell.setCellStyle(dataStyle);

                    contentCell = contentRow.createCell(2);
                    contentCell.setCellValue(position);
                    contentCell.setCellStyle(dataStyle);

                    contentCell = contentRow.createCell(4);
                    contentCell.setCellValue(period);
                    contentCell.setCellStyle(dataStyle);

                    contentCell = contentRow.createCell(5);
                    contentCell.setCellValue(description);
                    contentCell.setCellStyle(dataStyle);
//                    // 打印字段值
//                    System.out.println("Company: " + company);
//                    System.out.println("Position: " + position);
//                    System.out.println("Period: " + period);
//                    System.out.println("Description: " + description);
                }
            } else {
                for (int j = type, k =0 ; j < content.length; j++,k+=2) {
                    Cell contentCell = null;
                    if (j == content.length - 1){
                        contentCell = contentRow.createCell(5);
                    } else {
                        contentCell = contentRow.createCell(k);
                    }
//                    System.out.println("----content["+ j + "] "+content[j]);
                    contentCell.setCellValue(content[j]);
                    contentCell.setCellStyle(dataStyle);
                }
            }
        }

//        if (content[0].getClass().getName().equals(WorkExperience.class.getName())){
//            // 获取WorkExperience类的所有字段
//            Field[] fields = WorkExperience.class.getDeclaredFields();
//
//            // 遍历每个字段
//            for (Field field : fields) {
//                // 设置字段可访问，以便获取私有字段的值
//                field.setAccessible(true);
//
//                try {
//                    // 获取字段的值
//                    Object value = field.get(content);
//
//                    // 打印字段名和值
//                    System.out.println(field.getName() + ": " + value);
//                } catch (IllegalAccessException e) {
//                    e.printStackTrace();
//                }

//            for (int j = type, k =0 ; j < content.length; j++,k+=2) {
//                Cell contentCell = null;
//                if (j == content.length - 1){
//                    contentCell = contentRow.createCell(5);
//                } else {
//                    contentCell = contentRow.createCell(k);
//                }
//                System.out.println("----content["+ j + "] "+content[j]);
//                contentCell.setCellValue(content[j]);
//                contentCell.setCellStyle(dataStyle);
//            }
//        }

//        if (content.length == 0 /*|| type == 0*/){
//            if (type == 0 && content.length != 0 && i == content.length - 1){
//                contentRow = sheet.createRow(rowNum++);
//                sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,0,1));
//                sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,2,3));
//                sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,5,6));
//            }
//            Cell contentCell1 = contentRow.createCell(0);
//            contentCell1.setCellValue("");
//            contentCell1.setCellStyle(dataStyle);
//            Cell contentCell2 = contentRow.createCell(2);
//            contentCell2.setCellValue("");
//            contentCell2.setCellStyle(dataStyle);
//            Cell contentCell3 = contentRow.createCell(4);
//            contentCell3.setCellValue("");
//            contentCell3.setCellStyle(dataStyle);
//            Cell contentCell4 = contentRow.createCell(5);
//            contentCell4.setCellValue("");
//            contentCell4.setCellStyle(dataStyle);
//        }
    }

//    private static void mergedRegin2(Sheet sheet, CellStyle dataStyle, String[][] content, int i, int rowNum, Row contentRow, int type) {
//        sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,0,1));
//        sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,2,3));
//        sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,4,6));
//        if (content.length != 0){
//            for (int j = type, k = 0; j < content[i].length; j++, k+=2) {
//                Cell contentCell = contentRow.createCell(k);
//                contentCell.setCellValue(content[i][j]);
//                contentCell.setCellStyle(dataStyle);
//            }
//        }
//        if (content.length == 0 /*|| type == 0*/){
////            if (type == 0 && content.length != 0 && i == content.length - 1){
////                contentRow = sheet.createRow(rowNum++);
////                sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,0,1));
////                sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,2,3));
////                sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,4,6));
////            }
//            Cell contentCell1 = contentRow.createCell(0);
//            contentCell1.setCellValue("");
//            contentCell1.setCellStyle(dataStyle);
//            Cell contentCell2 = contentRow.createCell(2);
//            contentCell2.setCellValue("");
//            contentCell2.setCellStyle(dataStyle);
//            Cell contentCell3 = contentRow.createCell(4);
//            contentCell3.setCellValue("");
//            contentCell3.setCellStyle(dataStyle);
//        }
//    }
//
//    private static void mergedRegin3(Sheet sheet, CellStyle dataStyle, String[][] content, int i, int rowNum, Row contentRow, int type) {
//        sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,0,3));
//        sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,4,6));
//        if (content.length != 0){
//            for (int j = type, k = 0; j < content[i].length; j++, k+=4) {
//                Cell contentCell = contentRow.createCell(k);
//                contentCell.setCellValue(content[i][j]);
//                contentCell.setCellStyle(dataStyle);
//            }
//        }
//        if (content.length == 0 /*|| type == 0*/){
////            if (type == 0 && content.length != 0 && i == content.length - 1){
////                contentRow = sheet.createRow(rowNum++);
////                sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,0,3));
////                sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum-1,4,6));
////            }
//            Cell contentCell1 = contentRow.createCell(0);
//            contentCell1.setCellValue("");
//            contentCell1.setCellStyle(dataStyle);
//            Cell contentCell2 = contentRow.createCell(4);
//            contentCell2.setCellValue("");
//            contentCell2.setCellStyle(dataStyle);
//        }
//    }
//
//    private static void mergedRegin4(Sheet sheet, CellStyle dataStyle, String[] content, int rowNum, Row contentRow) {
//        sheet.addMergedRegion(new CellRangeAddress(rowNum-1,rowNum+2,0,6));
//        Cell contentCell = contentRow.createCell(content.length-1);
//        contentCell.setCellValue(content[content.length-1]);
//        contentCell.setCellStyle(dataStyle);
//    }

    /**
     * 解析Excel文件
     * @param fileBytes 文件字节数组
     * @return Workbook对象
     * @throws IOException IO异常
     */
    public static Workbook parseExcelFile(byte[] fileBytes) throws IOException {
        try (ByteArrayInputStream inputStream = new ByteArrayInputStream(fileBytes)) {
            return new XSSFWorkbook(inputStream);
        }
    }

    /**
     * 将Workbook对象保存为字节数组
     * @param workbook Workbook对象
     * @return 字节数组
     * @throws IOException IO异常
     */
    public static byte[] saveWorkbookToBytes(Workbook workbook) throws IOException {
        try (workbook; ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            workbook.write(outputStream);
            return outputStream.toByteArray();
        }
    }

    public static String getIdentificationNumber(MultipartFile file) throws IOException {
        InputStream inputStream = file.getInputStream();
        // 根据文件类型创建Workbook对象
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(5);
        if (row == null) {
            throw new RuntimeException("指定行不存在");
        }

        Cell cell = row.getCell(3);
        if (cell == null) {
            throw new RuntimeException("指定列不存在");
        }

        // 处理不同类型的内容
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "未知类型";
        }
    }
}
